package org.zjvis.datascience.common.sql;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ListUtils;
import org.apache.commons.lang3.StringUtils;
import org.zjvis.datascience.common.enums.PythonDateTypeFormatEnum;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.model.*;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.StringUtil;

import java.sql.Types;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description : 通用 SQL拼装帮助类
 * @date 2021-12-23
 */
public class SqlHelper {

    private SqlSyntaxHelper syntaxHelper = new SqlSyntaxHelper();

    private static final String NULL_STRING = "#NULL";

    private static final String SQL_TEMPLATE = "\nSELECT %s FROM %s \n%s \n%s \n%s";

    private static final String SQL_TEMPLATE_VIEW = "\nSELECT %s FROM (\n%s\n) tdbi_view \n%s \n%s \n%s";

    public void bind(Map<String, Integer> columnTypes) {
        syntaxHelper.setColumnTypes(columnTypes);
    }


    public String assembleSql(AggrConfig config, Table table) {
        List<DimensionConfig> dimensions = config.getDimensions();
        List<MeasureConfig> measures = config.getMeasures();
        List<ConfigComponent> filters = ListUtils.union(ListUtils.union(dimensions, measures), config.getFilters());

        String dimsStr = assembleDims(dimensions, table);
        String measuresStr = assembleMeasures(measures);
        String whereStr = assembleFilter(filters);

        String groupBy = StringUtils.EMPTY;
        if (CollectionUtils.isNotEmpty(dimensions)) {
            groupBy = "GROUP BY " + groupByDimsNo(dimensions);
        }

        String orderBy = assembleOrderBy(ListUtils.union(dimensions, measures));

        String selected = Joiner.on(", ").skipNulls()
                .join(StringUtils.isEmpty(dimsStr) ? null : dimsStr,
                        StringUtils.isEmpty(measuresStr) ? null : measuresStr);
        String sql = String
                .format(SQL_TEMPLATE, selected, table.getName(), whereStr, groupBy, orderBy);
        if (config.getLimit() != null) {
            sql = sql + " LIMIT " + config.getLimit();
        }
        return sql;
    }

    public String assembleFilter(AggrConfig config) {
        List<ConfigComponent> filters = ListUtils.union(
                ListUtils.union(config.getDimensions(), config.getMeasures()),
                config.getFilters());
        return assembleFilter(filters);
    }

    private String groupByDimsNo(List<DimensionConfig> dimsOrMeasures) {
        List<String> groupBy = Lists.newArrayList();
        for (int i = 0; i < dimsOrMeasures.size(); i++) {
            groupBy.add(String.valueOf(i + 1));
        }
        return Joiner.on(", ").join(groupBy);
    }

    private String groupByDimsName(List<DimensionConfig> dimsOrMeasures) {
        List<String> groupBy = Lists.newArrayList();
        for (DimensionConfig dim : dimsOrMeasures) {
            String groupByField = dim.getAlias();
            if (StringUtils.isEmpty(groupByField)) {
                groupByField = dim.getFieldName();
            }
            groupBy.add(groupByField);
        }
        return Joiner.on(", ").join(groupBy);
    }

    private String assembleDims(List<DimensionConfig> dimsOrMeasures, Table table) {
        List<String> dimSelectList = Lists.newArrayList();

        for (DimensionConfig dim : dimsOrMeasures) {
            String fieldName = dim.getFieldName();
            if (StringUtils.isNotEmpty(dim.getAlias())) {
                dimSelectList.add(fieldName + " as `" + dim.getAlias() + "`");
            }
            if (StringUtil.containsAllNumAndSpecial(fieldName)) {
                dimSelectList.add(" \"" + fieldName + "\"");
            } else {
                dimSelectList.add(fieldName);
            }
        }

        return Joiner.on(", ").join(dimSelectList);
    }

    private String assembleMeasures(List<MeasureConfig> measures) {
        List<String> measureSelectList = Lists.newArrayList();
        for (MeasureConfig measure : measures) {
            AggregatorHelper.Type aggType = AggregatorHelper.valueOf(measure.getAggType());

            boolean isExpr = StringUtils.isNotEmpty(measure.getExpr());
            String measureField = isExpr ? measure.getExpr() : measure.getFieldName();

            if (!isExpr) {
                if (AggregatorHelper.Type.SUM.equals(aggType)) {
                    measureField = "SUM(\"" + measureField + "\")";
                } else if (AggregatorHelper.Type.AVG.equals(aggType)) {
                    measureField = "AVG(\"" + measureField + "\")";
                } else if (AggregatorHelper.Type.MAX.equals(aggType)) {
                    measureField = "MAX(\"" + measureField + "\")";
                } else if (AggregatorHelper.Type.MIN.equals(aggType)) {
                    measureField = "MIN(\"" + measureField + "\")";
                } else if (AggregatorHelper.Type.COUNT.equals(aggType)) {
                    measureField = "COUNT(\"" + measureField + "\")";
                } else if (AggregatorHelper.Type.DISTINCT.equals(aggType)) {
                    measureField = "COUNT (DISTINCT \"" + measureField + "\")";
                }
            }

            String alias = measure.getAlias();
            if (StringUtils.isNotEmpty(alias)) {
                measureField = measureField + " as `" + alias + "`";
            }

            measureSelectList.add(measureField);
        }

        return Joiner.on(", ").join(measureSelectList);
    }

    public String assembleFilter(List<ConfigComponent> filters) {
        List<String> andFilterList = Lists.newArrayList();
        for (ConfigComponent cc : filters) {
            try {
                String andFilterStr = filter(cc);
                if (StringUtils.isNotEmpty(andFilterStr)) {
                    andFilterList.add(andFilterStr);
                }
            } catch (NullPointerException e) {
                if (cc instanceof DimensionConfig) {
                    throw DataScienceException.of(BaseErrorCode.DATASET_QUERY_ERROR, "数据表中，无 '" + ((DimensionConfig) cc).getFieldName() + "' 字段");
                }

            }
        }
        return andFilterList.isEmpty() ? StringUtils.EMPTY
                : "WHERE " + Joiner.on(" AND ").join(andFilterList);
    }


    public String assembleOrFilter(List<ConfigComponent> filters) {
        List<String> andFilterList = Lists.newArrayList();
        for (ConfigComponent cc : filters) {
            String andFilterStr = filter(cc);
            if (StringUtils.isNotEmpty(andFilterStr)) {
                andFilterList.add(andFilterStr);
            }
        }
        return andFilterList.isEmpty() ? StringUtils.EMPTY
                : "WHERE " + Joiner.on(" OR ").join(andFilterList);
    }

    /**
     * 对 “维度” + “度量” 进行排序
     *
     * @param dimensions
     * @return
     */
    private String assembleOrderBy(List<? extends DimensionConfig> dimensions) {
        List<String> orderBy = Lists.newArrayList();

        for (int i = 0; i < dimensions.size(); i++) {
            if (dimensions.get(i) instanceof MeasureConfig) {
                MeasureConfig dim = (MeasureConfig) dimensions.get(i);
                String sort = dim.getSort();
                if (StringUtils.isEmpty(sort)) {
                    orderBy.add((i + 1) + " desc");
                } else {
                    orderBy.add((i + 1) + " " + sort);
                }
            } else if (dimensions.get(i) instanceof DimensionConfig) {
                DimensionConfig dim = dimensions.get(i);
                String sort = dim.getSort();
                if (StringUtils.isEmpty(sort)) {
                    continue;
                }
                orderBy.add((i + 1) + " " + sort);
            }

        }
        String ret = Joiner.on(", ").join(orderBy);
        if (StringUtils.isNotEmpty(ret)) {
            ret = "ORDER BY " + ret;
        }
        return ret;
    }

    /**
     * 只排 ”维度“
     * //     * @param dimensions
     *
     * @return
     */
//    private String assembleOrderBy(List<? extends DimensionConfig> dimensions) {
//        List<String> orderBy = Lists.newArrayList();
//
//        for (int i = 0; i < dimensions.size(); i++) {
//            DimensionConfig dim = dimensions.get(i);
//            String sort = dim.getSort();
//            if (StringUtils.isEmpty(sort)) {
//                continue;
//            }
//            orderBy.add((i + 1) + " " + sort);
//        }
//        String ret = Joiner.on(", ").join(orderBy);
//        if (StringUtils.isNotEmpty(ret)) {
//            ret = "ORDER BY " + ret;
//        }
//        return ret;
//    }
    private String decorateValues(DimensionConfig dim) {
        List<String> decorates = Lists.newArrayList();
        for (int index = 0; index < dim.getValues().size(); index++) {
            String decorate = syntaxHelper.getFieldStr(dim, index);
            decorates.add(decorate);
        }
        return Joiner.on(", ").join(decorates);
    }

    private String filter(ConfigComponent conf) {
        if (conf instanceof DimensionConfig) {
            DimensionConfig dim = (DimensionConfig) conf;
            String fieldName = SqlUtil.formatPGSqlColName(dim.getFieldName());
            String filterType = dim.getFilterType();
            List<String> values = dim.getValues();
            if (StringUtils.isEmpty(filterType) || CollectionUtils.isEmpty(values)) {
                return null;
            }

            String v0 = dim.getValues().get(0);
            String v1 = "";
            String isNumeric = "";
            if (syntaxHelper.isNumber(dim)) {
                isNumeric = "::NUMERIC";
            }
            if (syntaxHelper.isDate(dim)) {
                v0 = String.format("pipeline.sys_func_format_time('%s', '%s')", v0, PythonDateTypeFormatEnum.FORMAT_0.getVal());
            } else if (!syntaxHelper.isNumber(dim)) {
                v0 = String.format("'%s'", v0);
            }
            if (NULL_STRING.equals(v0) || ("'" + NULL_STRING + "'").equals(v0)) {
                return fieldName + ("=".equals(dim.getFilterType()) ? " IS NULL" : " IS NOT NULL");
            }

            String percentageUseWhere = " 1=1";
            if (!dim.isIncludeNull()) {
                percentageUseWhere = fieldName + " is not null";
            }

            switch (filterType) {
                case "=":
                case "in":
                    return fieldName + " IN (" + decorateValues(dim) + ")";
                case "<>":
                    return fieldName + " NOT IN (" + decorateValues(dim) + ")";
                case ">":
                    return fieldName + " > " + v0 + isNumeric;
                case ">=":
                    if (syntaxHelper.isLongVarchar(dim)) {
                        return fieldName + " >= " + v0;
                    }
                    return fieldName + " >= " + v0 + isNumeric;
                case "<":
                    return fieldName + " < " + v0 + isNumeric;
                case "<=":
                    if (syntaxHelper.isLongVarchar(dim)) {
                        return fieldName + " <= " + v0;
                    }
                    return fieldName + " <= " + v0 + isNumeric;
                case "[a,b]":
                    v1 = syntaxHelper.getFieldStr(dim, 1);
                    return fieldName + " >= " + v0 + " AND " + fieldName + " <= " + v1;
                case "[a,b)":
                    v1 = syntaxHelper.getFieldStr(dim, 1);
                    return fieldName + " >= " + v0 + " AND " + fieldName + " < " + v1;
                case "![a,b)":
                    v1 = syntaxHelper.getFieldStr(dim, 1);
                    return fieldName + " < " + v0 + " OR " + fieldName + " >= " + v1;
                case "ilike":
                case "like":
                    if (syntaxHelper.isNumber(dim)) {
                        Matcher matcher = Pattern.compile("%%*").matcher(v0);
                        if (matcher.matches()) {
                            return fieldName + " ~ E''";
                        } else {
                            return fieldName + " ~ E'" + regularEscape(v0.split("%")[1]) + "'";
                        }
                    } else {
                        return fieldName + " ilike " + v0;
                    }
                case "not like":
                case "not ilike":
                    return fieldName + " not ilike " + v0;
                case "~":
                    return fieldName + " ~ E'" + regularEscape(v0) + "'";
                case "!~":
                    return fieldName + " !~ E'" + regularEscape(v0) + "'";
                case "starts with":
                    return fieldName + " ~ E'^" + regularEscape(v0) + "'";
                case "not starts with":
                    return fieldName + " !~ E'^" + regularEscape(v0) + "'";
                case "ends with":
                    return fieldName + " ~ E'" + regularEscape(v0) + "$'";
                case "not ends with":
                    return fieldName + " !~ E'" + regularEscape(v0) + "$'";
                case "a%-b%":
                    v1 = syntaxHelper.getFieldStr(dim, 1);
                    if ("-1".equals(v0)) {
                        return percentageUseWhere + " order by " + fieldName + " offset " + v1;
                    }
                    return percentageUseWhere + " order by " + fieldName + " limit " + v0 + " offset " + v1;
                case "0%-b%":
                    return percentageUseWhere + " order by " + fieldName + " limit " + v0;
                case "a%-100%":
                    v1 = syntaxHelper.getFieldStr(dim, 1);
                    return percentageUseWhere + " order by " + fieldName + " offset " + v1;
                default:
                    return null;
            }
        } else if (conf instanceof CompositeConfig) {
            CompositeConfig compositeConf = (CompositeConfig) conf;
            Iterator<ConfigComponent> iter = compositeConf.getIterator();
            List<String> iterList = Lists.newArrayList();
            while (iter.hasNext()) {
                ConfigComponent cc = iter.next();
                iterList.add(filter(cc));
            }
            return "(" + Joiner.on(" " + compositeConf.getType() + " ").join(iterList) + ")";
        }
        return null;
    }

    public Integer getColumnType(String key) {
        return syntaxHelper.getColumnType(key);
    }

    public static String stringEscapeHelper(String str) {
        if (StringUtils.isNotEmpty(str)) {
            // 单引号处理
            return str.replace("'", "''");
        }
        return "";
    }

    public static String regularEscape(String str) {
        if (StringUtils.isNotEmpty(str)) {
            if (str.contains(".")) {
                str = str.replaceAll("\\.", "\\\\\\\\.");
            }
            if (str.startsWith("'") && str.endsWith("'")) {
                if (str.length() >= 2) {
                    str = str.substring(1, str.length() - 1);
                } else {
                    str = str.replaceAll("\\\\'", "");
                }
            }
            // ^和$处理
            return str.replaceAll("\\^", "\\\\\\\\^")
                    .replaceAll("\\$", "\\\\\\\\\\$");
        }
        return "";
    }

    public static int mergeSqlType(int type) {
        switch (type) {
            case Types.BIGINT:
            case Types.SMALLINT:
            case Types.INTEGER:
                return Types.INTEGER;
            case Types.DECIMAL:
            case Types.NUMERIC:
            case Types.FLOAT:
            case Types.DOUBLE:
                return Types.DECIMAL;
            case Types.DATE:
                return Types.DATE;
            case Types.OTHER:
                return Types.OTHER;
            case Types.ARRAY:
                return Types.ARRAY;
            default:
                return Types.VARCHAR;
        }
    }

    public static String sqlType2StringType(int type) {
        String typeString = "";
        switch (type) {
            case Types.SMALLINT:
                typeString = "SMALLINT";
                break;
            case Types.BIGINT:
                typeString = "BIGINT";
                break;
            case Types.BOOLEAN:
                typeString = "BOOLEAN";
                break;
            case Types.VARCHAR:
            case Types.CHAR:
                typeString = "CHARACTER";
                break;
            case Types.BINARY:
                typeString = "BIT";
                break;
            case Types.INTEGER:
                typeString = "INTEGER";
                break;
            case Types.DOUBLE:
                typeString = "DOUBLE";
                break;
            case Types.NUMERIC:
                typeString = "NUMERIC";
                break;
            case Types.DATE:
                typeString = "DATE";
                break;
            default:
                typeString = "TEXT";

        }
        return typeString;
    }
}
